﻿using DTO;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Data;

namespace DAL
{
    public partial class CustomerService
    {
        CRMEntities db;
        public List<CustomerEntity> LoadCustomerByAll()
        {
            db = new CRMEntities();
            var query = (from c in db.Customers
                         join s in db.Sources on c.Source_ID equals s.Source_ID
                         join re in db.Regions on c.Province_Code equals re.Region_Code
                         select new CustomerEntity
                         {
                             Customer_ID = c.Customer_ID,
                             Source_ID = c.Source_ID,
                             Collector_ID = c.Collector_ID,
                             SourceName = s.SourceName,
                             CollectedDate = c.CollectedDate,
                             ClinicInfo = c.ClinicInfo,
                             FirstName = c.FirstName,
                             LastName = c.LastName,
                             Note = c.Note,
                             EDD = c.EDD,
                             IDNumber = c.IDNumber,
                             DateOfBirth = c.DateOfBirth,
                             CustomerStatus = c.CustomerStatus,
                             HomeNumber = c.HomeNumber,
                             SubStreet = c.Substreet,
                             Street = c.Street,
                             Ward_Code = c.Ward_Code,
                             District_Code = c.District_Code,
                             Province_Code = c.Province_Code,
                             HomePhoneNumber = c.HomePhoneNumber,
                             CellPhoneNumber = c.CellPhoneNumber,
                             OfficePhoneNumber = c.OfficePhoneNumber,
                             CurrentBrandName = c.CurrentBrandName,
                             PreviousBrandName = c.PreviousBrandName,
                             CallStatus = c.CallStatus,
                         }).ToList();

            return query;
        }

        public CustomerEntity LoadCustomerByID(long CustomerID)
        {
            db = new CRMEntities();
            if (!string.IsNullOrEmpty(CustomerID.ToString()))
            {
                var query = (from c in db.Customers
                             where c.Customer_ID == CustomerID
                             join re in db.Regions on c.Province_Code equals re.Region_Code
                             join s in db.Sources on c.Source_ID equals s.Source_ID
                             select new CustomerEntity
                             {
                                 //main info
                                 Customer_ID = c.Customer_ID,
                                 Source_ID = c.Source_ID,
                                 Collector_ID = c.Collector_ID,
                                 SourceName = s.SourceName,
                                 CollectedDate = c.CollectedDate,
                                 ClinicInfo = c.ClinicInfo,
                                 FirstName = c.FirstName,
                                 LastName = c.LastName,
                                 Note = c.Note,
                                 EDD = c.EDD,
                                 IDNumber = c.IDNumber,
                                 DateOfBirth = c.DateOfBirth,
                                 CustomerStatus = c.CustomerStatus,
                                 HomeNumber = c.HomeNumber,
                                 SubStreet = c.Substreet,
                                 Street = c.Street,
                                 Ward_Code = c.Ward_Code,
                                 District_Code = c.District_Code,
                                 Province_Code = c.Province_Code,
                                 HomePhoneNumber = c.HomePhoneNumber,
                                 CellPhoneNumber = c.CellPhoneNumber,
                                 OfficePhoneNumber = c.OfficePhoneNumber,
                                 CurrentBrandName = c.CurrentBrandName,
                                 PreviousBrandName = c.PreviousBrandName,
                                 CallStatus = c.CallStatus,
                                 //append-dix info
                                 ImportDate = s.ImportDate,
                             }).SingleOrDefault();
                return query;
            }
            else
            {
                return null;
            }
        }

        public CustomerEntity LoadCustomerByHomePhone(string HomePhoneNumber)
        {
            db = new CRMEntities();
            if (!string.IsNullOrEmpty(HomePhoneNumber))
            {
                var query = (from c in db.Customers
                             where c.HomePhoneNumber == HomePhoneNumber
                             join re in db.Regions on c.Province_Code equals re.Region_Code
                             join s in db.Sources on c.Source_ID equals s.Source_ID
                             select new CustomerEntity
                             {
                                 Customer_ID = c.Customer_ID,
                                 Source_ID = c.Source_ID,
                                 Collector_ID = c.Collector_ID,
                                 SourceName = s.SourceName,
                                 CollectedDate = c.CollectedDate,
                                 ClinicInfo = c.ClinicInfo,
                                 FirstName = c.FirstName,
                                 LastName = c.LastName,
                                 Note = c.Note,
                                 EDD = c.EDD,
                                 IDNumber = c.IDNumber,
                                 DateOfBirth = c.DateOfBirth,
                                 CustomerStatus = c.CustomerStatus,
                                 HomeNumber = c.HomeNumber,
                                 SubStreet = c.Substreet,
                                 Street = c.Street,
                                 Ward_Code = c.Ward_Code,
                                 District_Code = c.District_Code,
                                 Province_Code = c.Province_Code,
                                 HomePhoneNumber = c.HomePhoneNumber,
                                 CellPhoneNumber = c.CellPhoneNumber,
                                 OfficePhoneNumber = c.OfficePhoneNumber,
                                 CurrentBrandName = c.CurrentBrandName,
                                 PreviousBrandName = c.PreviousBrandName,
                                 CallStatus = c.CallStatus,
                                 //append-dix info
                                 ImportDate = s.ImportDate,
                             }).SingleOrDefault();

                return query;
            }
            else
            {
                return null;
            }
        }

        public CustomerEntity LoadCustomerByCellPhone(string CellPhoneNumber)
        {
            db = new CRMEntities();
            if (!string.IsNullOrEmpty(CellPhoneNumber))
            {
                var query = (from c in db.Customers
                             where c.CellPhoneNumber == CellPhoneNumber
                             join re in db.Regions on c.Province_Code equals re.Region_Code
                             join s in db.Sources on c.Source_ID equals s.Source_ID
                             select new CustomerEntity
                             {
                                 Customer_ID = c.Customer_ID,
                                 Source_ID = c.Source_ID,
                                 Collector_ID = c.Collector_ID,
                                 SourceName = s.SourceName,
                                 CollectedDate = c.CollectedDate,
                                 ClinicInfo = c.ClinicInfo,
                                 FirstName = c.FirstName,
                                 LastName = c.LastName,
                                 Note = c.Note,
                                 EDD = c.EDD,
                                 IDNumber = c.IDNumber,
                                 DateOfBirth = c.DateOfBirth,
                                 CustomerStatus = c.CustomerStatus,
                                 HomeNumber = c.HomeNumber,
                                 SubStreet = c.Substreet,
                                 Street = c.Street,
                                 Ward_Code = c.Ward_Code,
                                 District_Code = c.District_Code,
                                 Province_Code = c.Province_Code,
                                 HomePhoneNumber = c.HomePhoneNumber,
                                 CellPhoneNumber = c.CellPhoneNumber,
                                 OfficePhoneNumber = c.OfficePhoneNumber,
                                 CurrentBrandName = c.CurrentBrandName,
                                 PreviousBrandName = c.PreviousBrandName,
                                 CallStatus = c.CallStatus,
                                 //append-dix info
                                 ImportDate = s.ImportDate,
                             }).SingleOrDefault();

                return query;
            }
            else
            {
                return null;
            }
            
        }

        public CustomerEntity LoadCustomerByOfficePhone(string OfficePhoneNumber)
        {
            db = new CRMEntities();
            if (!string.IsNullOrEmpty(OfficePhoneNumber))
            {
                var query = (from c in db.Customers
                             where c.OfficePhoneNumber == OfficePhoneNumber
                             join re in db.Regions on c.Province_Code equals re.Region_Code
                             join s in db.Sources on c.Source_ID equals s.Source_ID
                             select new CustomerEntity
                             {
                                 Customer_ID = c.Customer_ID,
                                 Source_ID = c.Source_ID,
                                 Collector_ID = c.Collector_ID,
                                 SourceName = s.SourceName,
                                 CollectedDate = c.CollectedDate,
                                 ClinicInfo = c.ClinicInfo,
                                 FirstName = c.FirstName,
                                 LastName = c.LastName,
                                 Note = c.Note,
                                 EDD = c.EDD,
                                 IDNumber = c.IDNumber,
                                 DateOfBirth = c.DateOfBirth,
                                 CustomerStatus = c.CustomerStatus,
                                 HomeNumber = c.HomeNumber,
                                 SubStreet = c.Substreet,
                                 Street = c.Street,
                                 Ward_Code = c.Ward_Code,
                                 District_Code = c.District_Code,
                                 Province_Code = c.Province_Code,
                                 HomePhoneNumber = c.HomePhoneNumber,
                                 CellPhoneNumber = c.CellPhoneNumber,
                                 OfficePhoneNumber = c.OfficePhoneNumber,
                                 CurrentBrandName = c.CurrentBrandName,
                                 PreviousBrandName = c.PreviousBrandName,
                                 CallStatus = c.CallStatus,
                                 //append-dix info
                                 ImportDate = s.ImportDate,
                             }).SingleOrDefault();

                return query;
            }
            else
            {
                return null;
            }
        }

        public List<CustomerEntity> LoadListCustomerByStatus(int status)
        { 
            db = new CRMEntities();
            if (!string.IsNullOrEmpty(status.ToString()))
            {
                var query = (from c in db.Customers
                             where c.CustomerStatus == status
                             join re in db.Regions on c.Province_Code equals re.Region_Code
                             join s in db.Sources on c.Source_ID equals s.Source_ID
                             select new CustomerEntity
                             {
                                 Customer_ID = c.Customer_ID,
                                 Source_ID = c.Source_ID,
                                 Collector_ID = c.Collector_ID,
                                 SourceName = s.SourceName,
                                 CollectedDate = c.CollectedDate,
                                 ClinicInfo = c.ClinicInfo,
                                 FirstName = c.FirstName,
                                 LastName = c.LastName,
                                 Note = c.Note,
                                 EDD = c.EDD,
                                 IDNumber = c.IDNumber,
                                 DateOfBirth = c.DateOfBirth,
                                 CustomerStatus = c.CustomerStatus,
                                 HomeNumber = c.HomeNumber,
                                 SubStreet = c.Substreet,
                                 Street = c.Street,
                                 Ward_Code = c.Ward_Code,
                                 District_Code = c.District_Code,
                                 Province_Code = c.Province_Code,
                                 HomePhoneNumber = c.HomePhoneNumber,
                                 CellPhoneNumber = c.CellPhoneNumber,
                                 OfficePhoneNumber = c.OfficePhoneNumber,
                                 CurrentBrandName = c.CurrentBrandName,
                                 PreviousBrandName = c.PreviousBrandName,
                                 CallStatus = c.CallStatus,
                             }).ToList();
                return query;
            }
            else
            {
                return null;
            }
        }

        public List<CustomerEntity> LoadListCustomerByCallStatus(int callstatus)
        {
            db = new CRMEntities();
            if (!string.IsNullOrEmpty(callstatus.ToString()))
            {
                var query = (from c in db.Customers
                             where c.CallStatus == callstatus
                             join re in db.Regions on c.Province_Code equals re.Region_Code
                             join s in db.Sources on c.Source_ID equals s.Source_ID
                             select new CustomerEntity
                             {
                                 Customer_ID = c.Customer_ID,
                                 Source_ID = c.Source_ID,
                                 Collector_ID = c.Collector_ID,
                                 SourceName = s.SourceName,
                                 CollectedDate = c.CollectedDate,
                                 ClinicInfo = c.ClinicInfo,
                                 FirstName = c.FirstName,
                                 LastName = c.LastName,
                                 Note = c.Note,
                                 EDD = c.EDD,
                                 IDNumber = c.IDNumber,
                                 DateOfBirth = c.DateOfBirth,
                                 CustomerStatus = c.CustomerStatus,
                                 HomeNumber = c.HomeNumber,
                                 SubStreet = c.Substreet,
                                 Street = c.Street,
                                 Ward_Code = c.Ward_Code,
                                 District_Code = c.District_Code,
                                 Province_Code = c.Province_Code,
                                 HomePhoneNumber = c.HomePhoneNumber,
                                 CellPhoneNumber = c.CellPhoneNumber,
                                 OfficePhoneNumber = c.OfficePhoneNumber,
                                 CurrentBrandName = c.CurrentBrandName,
                                 PreviousBrandName = c.PreviousBrandName,
                                 CallStatus = c.CallStatus,
                             }).ToList();
                return query;
            }
            else
            {
                return null;
            }
        }

        public DataTable LoadCustomerListCAD(DateTime startDate, DateTime endDate, int userID, int resultdetail)
        {
            db = new CRMEntities();
            DataTable table = new DataTable();
            long _cusID = 0;

            table .Columns.Add("Selected",typeof(Boolean));
            table.Columns.Add("Customer_ID", typeof(long));
            table.Columns.Add("FirstName", typeof(string));
            table.Columns.Add("LastName", typeof(string));
            table.Columns.Add("IDNumber", typeof(string ));
            table.Columns.Add("Note", typeof(string));
            table.Columns.Add("CustomerStatus", typeof(int));
            table.Columns.Add("OfficePhoneNumber", typeof(string));
            table.Columns.Add("HomePhoneNumber", typeof(string));
            table.Columns.Add("CellPhoneNumber", typeof(string));
            table.Columns.Add("CallStatus", typeof(int));

            var query = (from call in db.OutBoundDetails
                         where call .CallDate >= startDate && call.CallDate <= endDate  && call .ResultDetail == resultdetail && call.User_ID == userID
                         join dw in db .DividingWorkings  on call .Customer_ID equals dw.Customer_ID
                         select call);
    
            foreach (var item in query.ToList() )
            {
                _cusID = (long)  item.Customer_ID;
            
                 var tmp = (from c in db.Customers
                            where c.Customer_ID == _cusID
                             join re in db.Regions on c.Province_Code equals re.Region_Code
                             join s in db.Sources on c.Source_ID equals s.Source_ID
                             select new 
                             {
                                 Selected = false,
                                 Customer_ID = c.Customer_ID,
                                 FirstName = c.FirstName,
                                 LastName = c.LastName,
                                 IDNumber = c.IDNumber,
                                 Note = c.Note,                          
                                 CustomerStatus = c.CustomerStatus,
                                 HomePhoneNumber = c.HomePhoneNumber,
                                 CellPhoneNumber = c.CellPhoneNumber,
                                 OfficePhoneNumber = c.OfficePhoneNumber,                                
                                 CallStatus = c.CallStatus,
                             }).SingleOrDefault();

                 table.Rows.Add(tmp.Selected, tmp.Customer_ID, tmp.FirstName, tmp.LastName, tmp.IDNumber,
                     tmp.Note, tmp.Customer_ID, tmp.HomePhoneNumber, tmp.CellPhoneNumber, tmp.OfficePhoneNumber, tmp.CallStatus);
            }

           return table;
         
        }

        public void DeleteCusFromDividingWorkings(long customerID)
        {
            CRMEntities db = new CRMEntities();
            var taskquery = db.DividingWorkings.Where(a => a.Customer_ID == customerID).SingleOrDefault();
            db.DividingWorkings.Remove(taskquery);
            db.SaveChanges();
        }

    }
}
